/**
 * 
 */
package danny.tools.db;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;

import danny.tools.log.LogAnalyser;
import danny.tools.log.ParsedData;

/**
 * @author Danny manage the database(mysql)
 */
public class DBMgr {

	private String userName;
	private String password;
	private String DBUrl;
	private Connection conn;
	private PreparedStatement insertStatement;

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDbUrl() {
		return DBUrl;
	}

	public void setDbUrl(String DBUrl) {
		this.DBUrl = DBUrl;
	}

	public DBMgr() {
		userName = "root";
		password = "123";
		DBUrl = "jdbc:mysql://localhost/log";
	}

	private Connection createConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(DBUrl, userName, password);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return conn;
	}

	public void getConnection() {
		if (conn == null) {
			conn = createConnection();
		}
	}

	protected void executeInsertStatement(String sql, List<Object> parameters) {
		if (insertStatement == null) {
			createInsertStatement(sql);
		}
		try {
			insertStatement.clearParameters();
			int columnIndex = 1;
			for (Iterator<Object> i = parameters.iterator(); i.hasNext(); columnIndex++) {
				Object eachParameter = i.next();
				insertStatement.setObject(columnIndex, eachParameter);
			}
			insertStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// directly implement
	public static void main(String args[]) {
		Connection conn = null;
		String userName="root";
		String password="123";
		String host="localhost";

		try {
			// get connection
			for (String s : args)
			{
				if ( s.startsWith("-u") ){
					 userName = s.substring(s.indexOf("-u")+2);
				}
				if ( s.startsWith("-p") ){
					password = s.substring(s.indexOf("-p")+2);
				}
				if ( s.startsWith("-h") ){
					host = s.substring(s.indexOf("-h")+2);
				}
			}
			
			//String userName = "root";
			//String password = "123";
			String url = "jdbc:mysql://"+host;
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");

			Statement stmt = conn.createStatement();

			// create db and table
			stmt.execute("create database if not exists log");
			stmt.execute("use log");
			stmt.execute("create table if not exists  `log`.`logs` ("
					+ "`id` bigint(20) NOT NULL auto_increment,"
					+ "`ip` varchar(50) default NULL,"
					+ "`time` datetime default NULL,"
					+ "`request` varchar(1024) default NULL,"
					+ "`ref_url` varchar(1024) default NULL,"
					+ "`http_code` int(5) default NULL,"
					+ "`ob_size` bigint(20) default NULL,"
					+ "`client_str` varchar(1024) default NULL,"
					+ "PRIMARY KEY  (`id`)"
					+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");

			// prepare data
			String insertSQL = "insert into logs(ip,time,request,ref_url,http_code,ob_size,client_str) values(?,?,?,?,?,?,?)";
			BufferedReader in = new BufferedReader(new InputStreamReader(
					new FileInputStream("F:/Ubuntu/head3000.log")));
			// List<ParsedData> parsedDatas = LogAnalyser.readLog(in);
			// conn.setAutoCommit(true);
			long start = System.currentTimeMillis();

			PreparedStatement insertStmt = conn.prepareStatement(insertSQL);

			// insert data
			String line;
			ParsedData data;
			do {
				line = in.readLine();
				data = LogAnalyser.parse(line);
				if (data != null) {
					insertStmt.setString(1, data.getIp());
					insertStmt.setTimestamp(2, data.getFormatedTime());
					insertStmt.setString(3, data.getRequestMethod());
					insertStmt.setString(4, data.getRefUrl());
					insertStmt.setString(5, data.getHTTPStatusCode());
					insertStmt.setString(6, data.getReturnedObjectSize());
					insertStmt.setString(7, data.getClientStr());
					insertStmt.executeUpdate();
				}
			} while (line != null);
			long end = System.currentTimeMillis();
			System.out.println(end - start + "ms");
		} catch (Exception e) {
			// System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
					System.out.println("Database connection terminated");
				} catch (Exception e) { /* ignore close errors */
				}
			}
		}
	}

	protected PreparedStatement createStatement(String sql) {
		PreparedStatement stmt = null;
		if (conn == null) {
			getConnection();
		}
		try {
			stmt = conn.prepareStatement(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return stmt;
	}

	protected void createInsertStatement(String sql) {
		if (sql.toLowerCase().indexOf("insert") != -1) {
			insertStatement = createStatement(sql);
		} else {
			System.out.println("sql seems not a insert statement: \n" + sql);
		}
	}

}
